package com.bici.Controller;

import com.bici.domain.User;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: keluosi@bicitech.cn
 * @date: 2018/4/12
 */
@RestController
public class ExcelController {

    @GetMapping("/downloadPicture")
    public void downloadPicture(HttpServletResponse response) {
        String fileName = "cat.jpg";
        response.setHeader("content-type", "application/octet-stream");
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        byte[] buff = new byte[1024];
        int length;
        BufferedInputStream bis = null;
        try {
            OutputStream os = response.getOutputStream();
            bis = new BufferedInputStream(new FileInputStream(new File("E:\\keluosi\\download\\" + fileName)));
            while ((length = bis.read(buff)) != -1) {
                os.write(buff, 0, length);
                os.flush();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (bis != null) {
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @GetMapping("/downloadExcel")
    public void downloadExcel(HttpServletResponse response) {
        String[] headers = {"ID", "姓名", "年龄"};

        // 创建一个Excel表格
        HSSFWorkbook workbook = new HSSFWorkbook();
        workbook.setForceFormulaRecalculation(true);

        // 创建每个格子的样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置字体样式
        Font font = workbook.createFont();
        //font.setColor(HSSFColor.RED.index);
        font.setBold(true);
        cellStyle.setFont(font);

        // 创建第一个sheet
        HSSFSheet sheet = workbook.createSheet("sheet1");
        // 创建表头行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(cellStyle);
        }

        // 创建数据行
        List<User> users = new ArrayList<>();
        users.add(new User(1001, "AAA", 20));
        users.add(new User(1002, "BBB", 21));
        users.add(new User(1003, "CCC", 22));
        users.add(new User(1004, "DDD", 23));
        users.add(new User(1005, "EEE", 24));
        User user = null;
        for (int i = 0; i < 3; i++) {
            user = users.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getName());
            row.createCell(2).setCellValue(user.getAge());
        }

        // 创建第二个sheet
        sheet = workbook.createSheet("sheet2");
        row = sheet.createRow(0);
        for (int i = 1; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i - 1);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(cellStyle);
        }
        for (int i = 3; i < 5; i++) {
            user = users.get(i);
            row = sheet.createRow(i - 2);
            row.createCell(0).setCellValue(user.getName());
            row.createCell(1).setCellValue(user.getAge());
        }

        // 创建第第三个sheet
        sheet = workbook.createSheet("sheet3");
        row = sheet.createRow(0);
        row.createCell(0).setCellValue("年龄合计");
        row.createCell(1).setCellFormula("SUM(sheet1!C2:C4,sheet2!B2:B3)");


        // 创建合计行
        // row = sheet.createRow(users.size() + 2);
        // row.createCell(0).setCellValue("合计");
        // row.createCell(2).setCellFormula("SUM(C3:C" + (users.size() + 2) + ")");

        try {
            ServletOutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            String filename = java.net.URLEncoder.encode("用户表.xls", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + filename);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}
